use sqlx::{
    postgres::{types::Oid as PgOId, PgRow},
    Row,
};
// https://www.postgresql.org/docs/current/functions-info.html

#[derive(serde::Serialize, serde::Deserialize)]
/// 代表数据库
pub struct Database {
    id: String,
    name: String,
    user: String,
    collate: String,
}

impl Database {
    pub fn from_row(row: PgRow) -> Self {
        Self {
            id: row.get::<PgOId, &str>("oid").0.to_string(),
            name: row.get("datname"),
            // user: row.get("rolname"),
            user: String::new(),
            collate: row.get("datcollate"),
        }
    }
}

#[derive(serde::Serialize, serde::Deserialize)]
pub struct Schema {
    name: String,
    owner: String,
}

impl Schema {
    pub fn from_row(row: PgRow) -> Self {
        Self {
            name: row.get("schema_name"),
            owner: row.get("schema_owner"),
        }
    }
}

#[derive(serde::Serialize, serde::Deserialize)]
pub struct Table {
    id: PgOId,
    name: String,
    owner: PgOId,
    spc_name: Option<String>,
}

impl Table {
    pub fn from_row(row: PgRow) -> Self {
        Self {
            id: row.get("oid"),
            name: row.get("relname"),
            owner: row.get("relowner"),
            spc_name: row.get("spcname"),
        }
    }
}

#[derive(serde::Serialize, serde::Deserialize)]
pub struct TableColumn {
    name: String,
    order: i32,
    type_: String,
    is_nullable: bool,
}

impl TableColumn {
    pub fn from_row(row: PgRow) -> Self {
        Self {
            name: row.get("column_name"),
            order: row.get("ordinal_position"),
            type_: row.get("udt_name"),
            is_nullable: row.get::<&str, &str>("is_nullable") == "NO",
        }
    }
}

/// 时序插件，超表结构
pub struct HyperTable {
    name: String,
    schema_name: String,
    column_name: String,
    interval_length: i64,
}

impl HyperTable {
    pub fn from_row(row: PgRow) -> Self {
        Self {
            name: row.get("table_name"),
            schema_name: row.get("schema_name"),
            column_name: row.get("column_name"),
            interval_length: row.get("interval_length"),
        }
    }

    pub fn get_create_sql(&self) -> String {
        format!("SELECT create_hypertable('\"{}\".\"{}\"', '{}', chunk_time_interval=> {}, migrate_data => true );",self.schema_name,&self.name,&self.column_name,&self.interval_length)
    }
}

/// 约束
///
/// SELECT a.*,pg_get_expr(a.conbin,a.conrelid) as conbin_text
/// FROM pg_constraint a
/// LEFT join pg_class b ON b.oid = a.conrelid
/// WHERE a.connamespace=2200
#[derive(sqlx::FromRow, Clone)]
#[allow(dead_code)]
pub struct Constraint {
    // #[sqlx(rename = "oid")]
    oid: PgOId,

    #[sqlx(rename = "conname")]
    pub name: String,

    /// c = check constraint, f = foreign key constraint, p = primary key constraint, u = unique constraint, t = constraint trigger, x = exclusion constraint
    #[sqlx(rename = "contype")]
    _type: i8,

    /// 约束是否可以推迟
    #[sqlx(rename = "condeferrable")]
    deferrable: Option<bool>,

    /// 默认情况下，约束是否可以推迟
    #[sqlx(rename = "condeferred")]
    deferred: Option<bool>,

    /// 约束是否经过验证，
    #[sqlx(rename = "convalidated")]
    validated: Option<bool>,

    /// 所属表的Id pg_class.oid
    #[sqlx(rename = "conrelid")]
    pub rel_id: PgOId,

    /// pg_type.oid
    #[sqlx(rename = "contypid")]
    type_id: PgOId,

    /// 索引id，pg_class.oid
    #[sqlx(rename = "conindid")]
    ind_id: PgOId,

    /// 分区表主表的约束id pg_constraite.oid
    #[sqlx(rename = "conparentid")]
    parent_id: PgOId,

    /// 外键约束，关联表的id pg_class.oid
    #[sqlx(rename = "confrelid")]
    frel_id: PgOId,

    /// 外键约束，更新时的行为，a = no action, r = restrict, c = cascade, n = set null, d = set default
    #[sqlx(rename = "confupdtype")]
    fupd_type: i8,

    /// 外键约束，删除时的行为，a = no action, r = restrict, c = cascade, n = set null, d = set default
    #[sqlx(rename = "confdeltype")]
    fdel_type: i8,

    /// 外键约束，匹配方式，f = full, p = partial, s = simple
    #[sqlx(rename = "confmatchtype")]
    fmatch_type: i8,

    ///是否为本地定义的
    #[sqlx(rename = "conislocal")]
    is_local: bool,

    ///直接继承的约束的数量
    #[sqlx(rename = "coninhcount")]
    inh_count: i32,

    /// 表示该约束不是继承来的
    #[sqlx(rename = "connoinherit")]
    no_inherit: bool,

    /// 表示该约束相关联的列 pg_attribute.attnum
    #[sqlx(rename = "conkey")]
    pub keys: Option<Vec<i16>>,

    /// 外键约束，相关联的列 pg_attribute.attnum
    #[sqlx(rename = "confkey")]
    fkeys: Option<Vec<i16>>,

    /// 外键约束，主键和外键比较的相等运算符列表
    #[sqlx(rename = "conpfeqop")]
    pfeqop: Option<Vec<PgOId>>,

    /// 外键约束，主键和主键比较的相等运算符列表
    #[sqlx(rename = "conppeqop")]
    ppeqop: Option<Vec<PgOId>>,

    /// 外键约束，外键和外键比较的相等运算符列表
    #[sqlx(rename = "conffeqop")]
    ffeqop: Option<Vec<PgOId>>,

    /// 外键约束，如果 delete action 设置为 SET NULL 或 SET DEFAULT，
    ///
    /// pg_attribute.attnum
    // #[sqlx(rename = "confdelsetcols")]
    // fdel_set_cols: Option<Vec<i16>>,

    /// 排斥约束，列出每一列的排斥操作符
    ///
    /// pg_operator.oid
    #[sqlx(rename = "conexclop")]
    exclop: Option<Vec<PgOId>>,

    /// Check约束，
    #[sqlx(rename = "conbin_text")]
    bin: Option<String>,
}

impl Constraint {
    /// 是否为主键约束
    #[allow(dead_code)]
    pub fn is_primary_key(&self) -> bool {
        self._type == ('p' as i8)
    }

    ///ALTER TABLE public."TEST" ADD CONSTRAINT 键_name UNIQUE ("column1","column2");
    ///ALTER TABLE public."TEST" ADD CONSTRAINT 键_name PRIMARY KEY ("column1","column2");
    ///修改时先删除再重新创建 DROP CONSTRAINT
    pub fn to_sql(&self) -> String {
        if self.is_primary_key() {
            format!("ADD CONSTRAINT \"{}\" PRIMARY KEY", &self.name)
        } else {
            format!("ADD CONSTRAINT \"{}\" UNIQUE", &self.name)
        }
    }

    pub fn is_not_equal(&self, constraint: &Constraint) -> bool {
        if self.name != constraint.name {
            false
        } else if self.is_primary_key() != constraint.is_primary_key()
            || is_value_equal(&self.keys, &self.keys) == false
        {
            true
        } else {
            false
        }
    }
}

fn is_value_equal(v1: &Option<Vec<i16>>, v2: &Option<Vec<i16>>) -> bool {
    if (v1.is_some() && v2.is_none()) || (v1.is_none() && v2.is_some()) {
        return false;
    }
    if v1.is_none() && v2.is_none() {
        return true;
    }
    let v1 = v1.as_ref().unwrap();
    let v2 = v2.as_ref().unwrap();
    if v1.len() != v2.len() {
        return false;
    }
    for i in v1 {
        if v2.contains(&i) == false {
            return false;
        }
    }
    true
}
